package db.tables;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Timestamp;
import java.util.ArrayList;
import java.util.Calendar;

import vcp.controllers.Statistics;

import com.mysql.jdbc.CacheAdapter;

import control.ConnectionManage;
import db.parking.beans.Orders;
import db.parking.beans.StatsticPerDay;
import db.parking.beans.WeeklyStatistics;

/**
 * AdminWeeklyStatistics manages all requests From,To table Weekly Statistics
 * @author Operations
 *
 */
public class AdminWeeklyStatistics {
	private static Connection conn = ConnectionManage.getInstance().getConnection();


	/**	week statics between 2 times
	 * @param startTime
	 * @param leaveTime
	 * @param lotNo
	 * @return WeeklyStatistics array for all statistics between 2 Timestamp 
	 * @throws SQLException
	 */
	public static WeeklyStatistics[] getWeeklyStatistics(Timestamp startTime, Timestamp leaveTime, int lotNo) throws SQLException {
		String sql = "SELECT * FROM statsticsperweek WHERE startweek >= ? AND startweek < ? AND lotNo = ? ORDER BY startweek";

		ResultSet rs = null;
		try (PreparedStatement stmt = conn.prepareStatement(sql);) {
			stmt.setTimestamp(1, startTime);
			stmt.setTimestamp(2, leaveTime);
			stmt.setInt(3, lotNo);
			rs = stmt.executeQuery();
			rs.last();
			int totalRows = rs.getRow();
			rs.beforeFirst();
			WeeklyStatistics bean[] = new WeeklyStatistics[totalRows];
			for (int i = 0; i < totalRows; i++)
				bean[i] = new WeeklyStatistics();
			int rowCount = 0;
			while (rs.next()) {
				bean[rowCount].setStartWeek(rs.getTimestamp("startweek"));
				bean[rowCount].setReportName(rs.getString("reportname"));
				bean[rowCount].setAverage(rs.getFloat("average"));
				bean[rowCount].setMedian(rs.getFloat("median"));
				bean[rowCount].setStandardDeviation(rs.getFloat("standarddeviation"));
				int []tmp=new int[10];
				tmp[0]=rs.getInt("freq1");
				tmp[1]=rs.getInt("freq2");
				tmp[2]=rs.getInt("freq3");
				tmp[3]=rs.getInt("freq4");
				tmp[4]=rs.getInt("freq5");
				tmp[5]=rs.getInt("freq6");
				tmp[6]=rs.getInt("freq7");
				tmp[7]=rs.getInt("freq8");
				tmp[8]=rs.getInt("freq9");
				tmp[9]=rs.getInt("freq10");
				bean[rowCount].setFreq(tmp);
				rowCount++;
			}
			return bean;
		} catch (SQLException e) {
			System.err.println(e);
			System.err.println();
			return null;
		} finally {
			if (rs != null) {
				rs.close();
			}
		}
	}
	
	
	/**
	 * @return	week statics array
	 * @throws SQLException sqlexception
	 */
	public static WeeklyStatistics[] getAllWeeklyStatistics() throws SQLException {
		String sql = "SELECT * FROM statsticsperweek ORDER BY startweek";
		ResultSet rs = null;
		try (PreparedStatement stmt = conn.prepareStatement(sql);) {
			rs = stmt.executeQuery();
			rs.last();
			int totalRows = rs.getRow();
			rs.beforeFirst();
			WeeklyStatistics bean[] = new WeeklyStatistics[totalRows];
			for (int i = 0; i < totalRows; i++)
				bean[i] = new WeeklyStatistics();
			int rowCount = 0;
			while (rs.next()) {
				bean[rowCount].setStartWeek(rs.getTimestamp("startweek"));
				bean[rowCount].setReportName(rs.getString("reportname"));
				bean[rowCount].setAverage(rs.getFloat("average"));
				bean[rowCount].setMedian(rs.getFloat("median"));
				bean[rowCount].setStandardDeviation(rs.getFloat("standarddeviation"));
				int []tmp=new int[10];
				tmp[0]=rs.getInt("freq1");
				tmp[1]=rs.getInt("freq2");
				tmp[2]=rs.getInt("freq3");
				tmp[3]=rs.getInt("freq4");
				tmp[4]=rs.getInt("freq5");
				tmp[5]=rs.getInt("freq6");
				tmp[6]=rs.getInt("freq7");
				tmp[7]=rs.getInt("freq8");
				tmp[8]=rs.getInt("freq9");
				tmp[9]=rs.getInt("freq10");
				bean[rowCount].setFreq(tmp);
				rowCount++;
			}
			return bean;
		} catch (SQLException e) {
			System.err.println(e);
			System.err.println();
			return null;
		} finally {
			if (rs != null) {
				rs.close();
			}
		}
	}

	/**
	 * @return 	true if insert to data base is gone well, false if not
	 * @throws Exception	sql 
	 */
	@SuppressWarnings("deprecation")
	public static boolean insertStasticsPerDay() throws Exception {	//insert statistics per one day	done
		// complaintID is autoIncremented on Insert
		String sql = "INSERT into statsticsperday " +
				"(lotNo , date , realizedordersnum , canceledordersnum, subscribersnum, subscriberscarsnum, latenessnum) "
				+ "VALUES (?, ?, ?, ?, ?,?,?)";
		
		ResultSet rs = null;
		long time = System.currentTimeMillis(); // create current time
		Timestamp endDay = new Timestamp(time); // set time like 2014-12-01
													// 00:00:00
		endDay.setHours(0);
		endDay.setMinutes(0);
		endDay.setSeconds(0);
		endDay.setNanos(0);
		
		
		Timestamp startDay = new Timestamp(endDay.getTime());
		Calendar cal = Calendar.getInstance();
		cal.setTime(startDay);
		cal.add(Calendar.DAY_OF_WEEK, -1); // add day for the first week
		startDay.setTime(cal.getTime().getTime()); // add day for the first week
		
		
		Timestamp startMinus28 =new Timestamp(endDay.getTime());
		Calendar cal2 = Calendar.getInstance();
		cal2.setTime(startDay);
		cal.add(Calendar.DAY_OF_WEEK, -28); // add day for the first week
		startMinus28.setTime(cal.getTime().getTime()); // add day for the first week
		
	

		try (PreparedStatement stmt = conn.prepareStatement(sql,
				Statement.RETURN_GENERATED_KEYS);) {
			for (int i = 1; i <= 6 ; i++) { // run for all lots
				stmt.setInt(1, i);		//lotNo
				stmt.setTimestamp(2, startDay);	//day date
				int realizedorders=AdminOrders.numberOfRealizedOrders(startDay,endDay, i);  //get num of realizedorders
				stmt.setInt(3, realizedorders);	//set num realizedorders	
				
				int canceledorders=AdminCanceledOrders.canceldOrdersCount(startDay,endDay, i);  //get num of canceledorders
				stmt.setInt(4, canceledorders);	//set num of canceledorders
				
				int subscribers= AdminSubscriber.getSubscribersNumInLot(startDay,startMinus28, i); //get num of subscribers
				stmt.setInt(5, subscribers);	//set num of subscribers
				
				int subscriberscars= AdminSubscriber.getSubscribersNumInLotMoreThanOneCar(startDay,startMinus28, i); //get num of subscribers that have more than one car
				stmt.setInt(6, subscriberscars);	//set num of subscribers
				
				int lateness = AdminOrders.numberOfParkingLateness(startDay, endDay, i); //get num of lateness
				stmt.setInt(7, lateness );	//set num of lateness

						stmt.executeUpdate();
				
			}
		} catch (SQLException e) {
			System.err.println(e + "AdminStatistics-> insertStasticsPerDay");
			return false;
		} finally {
			if (rs != null)
				rs.close();
		}
		return true;

	}

	
	/**
	 * @return true if all statistics have been inserted
	 * @throws Exception
	 */
	@SuppressWarnings("deprecation")
	public static boolean insertStasticsPerWeek() throws Exception { 
	// complaintID is autoIncremented on Insert
			String sql = "INSERT into statsticsperweek"
					+ "(lotNo , startweek , reportname , average, median, standarddeviation, freq1, freq2,freq3,freq4,freq5,freq6,freq7,freq8,freq9,freq10) "
					+ "VALUES (?,?, ?, ?, ?, ?,?,?,?,?,?,?,?,?,?,?)";
			ResultSet rs = null;
			long time = System.currentTimeMillis(); // create current time
			Timestamp endDay = new Timestamp(time); // set tine like 2014-12-01	
			endDay.setHours(0);
			endDay.setMinutes(0);
			endDay.setSeconds(0);
			endDay.setNanos(0);
			
			Timestamp startDay = new Timestamp(endDay.getTime());
			Calendar cal = Calendar.getInstance();
			cal.setTime(startDay);
			cal.add(Calendar.DAY_OF_WEEK, -50); // back to the start of the week
			startDay.setTime(cal.getTime().getTime()); // add day for the first week
			

		try (PreparedStatement stmt = conn.prepareStatement(sql,
				Statement.RETURN_GENERATED_KEYS);) {
			for (int i = 1; i < 6; i++) { // run for all lots
				StatsticPerDay[] dayStastics = getStatsticDuringTime(startDay,endDay);
				
				if(dayStastics!=null){
				
				//arrays for the sum of 7 days
				int[] canceloders=new int[dayStastics.length];
				int[] LatenessNo=new int[dayStastics.length];
				int[] RealizedordersNo=new int[dayStastics.length];
				int[] SubscriberscarsNo=new int[dayStastics.length];
				int[] SubscribersNo=new int[dayStastics.length];
				
				//fill the arrays per 1 lot for the sum of 7 days
				for(int j=0;j<dayStastics.length;j++){		//run for all the statistics per one day
					canceloders[i]= dayStastics[i].getCanceledordersNo();
					LatenessNo[i]= dayStastics[i].getLatenessNo();
					RealizedordersNo[i]= dayStastics[i].getRealizedordersNo();
					SubscriberscarsNo[i]= dayStastics[i].getSubscriberscarsNo();
					SubscribersNo[i]= dayStastics[i].getSubscribersNo();
				}
				//lotNo , startweek , reportname , average, median, standarddeviation, freq1"
				
				//set cancel orders per 1 week
				stmt.setInt(1, i);		//lotNo
				stmt.setTimestamp(2, startDay);		//lotNo
				stmt.setString(3, "canceloders");
				stmt.setFloat(4, Statistics.average(canceloders));
				stmt.setFloat(5, Statistics.median(canceloders));
				stmt.setFloat(6, Statistics.standardDeviation(canceloders));
				int [] freq =Statistics.frequency(canceloders);
				for(int q=7;q<17;q++)
					stmt.setInt(q, freq[q-7]);
				stmt.executeUpdate();

				//set LatenessNo per 1 week
				stmt.setInt(1, i);		//lotNo
				stmt.setTimestamp(2, startDay);		//lotNo
				stmt.setString(3, "LatenessNo");
				stmt.setFloat(4, Statistics.average(LatenessNo));
				stmt.setFloat(5, Statistics.median(LatenessNo));
				stmt.setFloat(6, Statistics.standardDeviation(LatenessNo));
				freq =Statistics.frequency(LatenessNo);
				for(int q=7;q<17;q++)
					stmt.setInt(q, freq[q-7]);
				stmt.executeUpdate();
				
				//set RealizedordersNo per 1 week
				stmt.setInt(1, i);		//lotNo
				stmt.setTimestamp(2, startDay);		//lotNo
				stmt.setString(3, "RealizedordersNo");
				stmt.setFloat(4, Statistics.average(RealizedordersNo));
				stmt.setFloat(5, Statistics.median(RealizedordersNo));
				stmt.setFloat(6, Statistics.standardDeviation(RealizedordersNo));
				freq =Statistics.frequency(RealizedordersNo);
				for(int q=7;q<17;q++)
					stmt.setInt(q, freq[q-7]);
				stmt.executeUpdate();
				
				//set SubscriberscarsNo per 1 week
				stmt.setInt(1, i);		//lotNo
				stmt.setTimestamp(2, startDay);		//lotNo
				stmt.setString(3, "SubscriberscarsNo");
				stmt.setFloat(4, Statistics.average(SubscriberscarsNo));
				stmt.setFloat(5, Statistics.median(SubscriberscarsNo));
				stmt.setFloat(6, Statistics.standardDeviation(SubscriberscarsNo));
				freq =Statistics.frequency(SubscriberscarsNo);
				for(int q=7;q<17;q++)
					stmt.setInt(q, freq[q-7]);
				stmt.executeUpdate();
				
				//set SubscribersNo per 1 week
				stmt.setInt(1, i);		//lotNo
				stmt.setTimestamp(2, startDay);		//lotNo
				stmt.setString(3, "SubscribersNo");
				stmt.setFloat(4, Statistics.average(SubscribersNo));
				stmt.setFloat(5, Statistics.median(SubscribersNo));
				stmt.setFloat(6, Statistics.standardDeviation(SubscribersNo));
				freq =Statistics.frequency(SubscribersNo);
				for(int q=7;q<17;q++)
					stmt.setInt(q, freq[q-7]);
				stmt.executeUpdate();
			}
			}
		} catch (SQLException e) {
			System.err.println(e + "AdminStatistics-> insertStasticsPerWeek");
			return false;
		} finally {
			if (rs != null)
				rs.close();
		}
		return true;
	}
	
		
	
	/**
	 * @param firstDay lastDay
	 *            return all the statisticsPerDay between 2 Timestamp 
	 * @return statisticsPerDay array
	 */
	@SuppressWarnings("deprecation")
	public static StatsticPerDay [] getStatsticDuringTime(Timestamp firstDay, Timestamp lastDay) throws SQLException{
		String sql = "SELECT * " +
				"FROM statsticsperday "
				+ "WHERE date>=? and date <=?";
		ResultSet rs = null;
		firstDay.setHours(0);
		firstDay.setMinutes(0);
		firstDay.setSeconds(0);
		firstDay.setNanos(0);// 00:00:00
		
		lastDay.setHours(0);
		lastDay.setMinutes(0);
		lastDay.setSeconds(0);
		lastDay.setNanos(0);// 00:00:00

		try (PreparedStatement stmt = conn.prepareStatement(sql,
				Statement.RETURN_GENERATED_KEYS);) {
				stmt.setTimestamp(1, firstDay); // day date
				stmt.setTimestamp(2, lastDay); // day date
				rs = stmt.executeQuery();
				rs.last();
				int totalRows = rs.getRow();
				rs.beforeFirst();
				StatsticPerDay bean[] = new StatsticPerDay [totalRows];
				for (int i = 0; i < totalRows; i++)
					bean[i] = new StatsticPerDay();
				int rowCount = 0;
				while (rs.next()) {
					bean[rowCount].setLatenessNo(rs.getInt("lotNo"));
					bean[rowCount].setDate(rs.getTimestamp("date"));
					bean[rowCount].setRealizedordersNo(rs.getInt("realizedordersnum"));
					bean[rowCount].setCanceledordersNo(rs.getInt("canceledordersnum"));
					bean[rowCount].setSubscribersNo(rs.getInt("subscribersnum"));
					bean[rowCount].setSubscriberscarsNo(rs.getInt("subscriberscarsnum"));
					bean[rowCount].setLatenessNo(rs.getInt("latenessnum"));
					rowCount++;
				}
				return bean;
			} catch (SQLException e) {
				System.err.println(e);
				return null;
			} finally {
				if (rs != null) {
					rs.close();
				}
			}
		}
	
	
	/**
	 * @param firstDay lastDay
	 *            return all the 
	 * @return statisticsPerDay array
	 */

	public static StatsticPerDay [] getAllStatsticPerDay() throws SQLException{
		String sql = "SELECT * FROM stasticsperday ";
		ResultSet rs = null;
		try (PreparedStatement stmt = conn.prepareStatement(sql,
				Statement.RETURN_GENERATED_KEYS);) {
				rs = stmt.executeQuery();
				rs.last();
				int totalRows = rs.getRow();
				rs.beforeFirst();
				StatsticPerDay bean[] = new StatsticPerDay [totalRows];
				for (int i = 0; i < totalRows; i++)
					bean[i] = new StatsticPerDay();
				int rowCount = 0;
				while (rs.next()) {
					bean[rowCount].setLatenessNo(rs.getInt("lotNo"));
					bean[rowCount].setDate(rs.getTimestamp("date"));
					bean[rowCount].setRealizedordersNo(rs.getInt("realizedordersNo"));
					bean[rowCount].setCanceledordersNo(rs.getInt("canceledordersNo"));
					bean[rowCount].setSubscribersNo(rs.getInt("subscribersNo"));
					bean[rowCount].setSubscriberscarsNo(rs.getInt("subscriberscarsNo"));
					bean[rowCount].setLatenessNo(rs.getInt("latenessNo"));
					rowCount++;
				}
				return bean;
			} catch (SQLException e) {
				System.err.println(e);
				return null;
			} finally {
				if (rs != null) {
					rs.close();
				}
			}
		}
}
	
	
